package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

/**
 * 数据库操作工具类
 * @author lamp
 *
 */
public class DBUtils {
    
    //数据库连接地址
    public static String url;
    //用户名
    public static String username;
    //密码
    public static String password;
    //mysql的驱动类
    public static String driver;
    
    //private static ResourceBundle rb = ResourceBundle.getBundle("com.util.db.db-config");
    
    
    //使用静态块加载驱动程序
    static{
        url = "jdbc:mysql://localhost:3306/test";
        username = "root";
        password = "root";
        driver = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

	public DBUtils() {
		// TODO Auto-generated constructor stub
	}

	//定义一个获取数据库连接的方法
    public static Connection getConnection(){
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
                //2.创建statement类对象，用来执行SQL语句！！
                Statement statement = con.createStatement();
                //要执行的SQL语句
                String sql = "select * from students";
                //3.ResultSet类，用来存放获取的结果集！！
                ResultSet rs = statement.executeQuery(sql);
                System.out.println("-----------------");
                System.out.println("执行结果如下所示:");  
                System.out.println("-----------------");  
                System.out.println("学号" + "\t" + "姓名"+ "\t" + "班级"+ "\t" + "性别"+ "\t" + "Java成绩"+ "\t" + "数学成绩"+ "\t" + "英语成绩");  
                System.out.println("-----------------");  
                 
                int id = 0;
                String stunum = null;
                String name = null;
                int classes = 0;
                String gender = null;
                double javaScore = 0, mathScore=0, englishScore=0;
             
                while(rs.next()){
                    //获取stuname这列数据
                    stunum = rs.getString("stunum");
                    //获取stuid这列数据
                    name = rs.getString("name");
                    classes = rs.getInt("classes");
                    gender = rs.getString("gender");
                    javaScore = rs.getDouble("javaScore");
                    mathScore = rs.getDouble("mathScore");
                    englishScore = rs.getDouble("englishScore");

                    //输出结果
                    System.out.println(stunum + "\t" + name + "\t" + classes + "\t" + gender + "\t" + javaScore + "\t" + mathScore + "\t" + englishScore + "\n");
                }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("获取连接失败");
        }
        return con;
        }
    
    /**
     * 关闭数据库连接
     * @param rs
     * @param stat
     * @param conn
     */
    public static void close(ResultSet rs,Statement stat,Connection conn){
            try {
                if(rs!=null)rs.close();
                if(stat!=null)stat.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
    
}